﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Npgsql;
using frmPrincipalTestes;

namespace frmPrincipal
{
    public partial class frmCadCompras : Form
    {
        private StandardDBConnection standardDBConnection = new StandardDBConnection();
        private float total;
        private List<float> selectedProductsPrices = new List<float>();
        private List<int> selectedProductsAmounts = new List<int>();
        private List<int> selectedProductsCodes = new List<int>();

        public frmCadCompras()
        {
            this.total = 0;
            InitializeComponent();
        }

        private void frmCadCompras_Load(object sender, EventArgs e)
        {
            DateTime timeNow = DateTime.Now;
            lblDataSale.Text = timeNow.ToString("dd/MM/yyyy");
        }

        //Funções Auxiliares
        private void TotalRefresh()
        {
            this.total = 0;
            for (int count = 0; count < selectedProductsAmounts.Count; count++)
            {
                this.total += selectedProductsAmounts[count] * selectedProductsPrices[count];
            }

            lblTotal.Text = string.Format("Valor Total: R$ {0:#,##0.00}", this.total);
        }
        private void CleanFields()
        {
            txtCNPJ.Text = "";
            txtCNPJ.Enabled = true;
            lblForn.Text = "";
            txtProduct.Text = "";
            lstFoundProducts.Items.Clear();
            lstSelected.Items.Clear();
            this.selectedProductsAmounts.Clear();
            this.selectedProductsPrices.Clear();
            TotalRefresh();
        }
        private bool forneceOK()
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            NpgsqlDataReader databaseDataReader;
            string sqlCommandText;

            sqlCommandText = "SELECT * FROM produto LEFT JOIN fornece ON (cod_prod = cod_produto) " +
                        "WHERE nome_prod ILIKE '%" + txtProduct.Text + "%' " +
                        "ORDER BY nome_prod ";

            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Connection = standardDBConnection.cnn;
            sqlCommand.CommandText = sqlCommandText;
            databaseDataReader = sqlCommand.ExecuteReader();

            sqlCommand.Dispose();
            if (!databaseDataReader.HasRows)
            {
                databaseDataReader.Close();
                databaseDataReader.Dispose();
                return false;
            }
            else
            {
                databaseDataReader.Close();
                databaseDataReader.Dispose();
                return true;
            }
        }
        private float GetPrice(ListBox listbox, int selected)
        {
            string selectedItemPrice, selectedItemText;
            float productPrice = 0;

            selectedItemText = listbox.Items[selected].ToString();

            for (int count = 0; count < selectedItemText.Length; count++)
            {
                if (selectedItemText[count] == 'R' && selectedItemText[count + 2] == ' ')
                {
                    selectedItemPrice = selectedItemText.Substring(count + 3, selectedItemText.Length - count - 3);
                    productPrice = Convert.ToSingle(selectedItemPrice);
                }
            }

            return productPrice;
        }

        //Preenche fornecedor(chama frmCadFornecedores se não existir)
        private void txtCNPJ_Leave(object sender, EventArgs e)
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            NpgsqlDataReader databaseDataReader;
            frmCadFornecedores f;
            string sqlCommandText;
            DialogResult resp = new DialogResult();

            
            if (!Testes.ValidCNPJ(txtCNPJ.Text))
            {
                MessageBox.Show("Forneça um código válido!", "Pesquisa Fornecedor", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            this.Cursor = Cursors.WaitCursor;

            sqlCommandText = "SELECT * FROM fornecedor WHERE cnpj = '" + txtCNPJ.Text+"' ";
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Connection = standardDBConnection.cnn;
            sqlCommand.CommandText = sqlCommandText;
            databaseDataReader = sqlCommand.ExecuteReader();

            if (databaseDataReader.HasRows)
            {
                databaseDataReader.Read();
                lblForn.Text = databaseDataReader["nome_forn"].ToString();
            }else{
                resp = MessageBox.Show("Fornecedor não encontrado. Deseja cadastrá-lo?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (resp == DialogResult.Yes)
                {
                    f = new frmCadFornecedores(txtCNPJ.Text);
                    f.FormClosing += (ClosingSender, ClosingE) => lblForn.Text = f.GetLastSupplierName();
                    f.Show();
                }
                else
                    return;
            }

            txtCNPJ.Enabled = false;
            databaseDataReader.Close();
            databaseDataReader.Dispose();
            sqlCommand.Dispose();
            this.Cursor = Cursors.Default;
        }

        //Pesquisa dos produtos
        private void PesquisarProducts()
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            NpgsqlDataReader databaseDataReader;
            string sqlCommandText, aux;
            int auxcodigo;
            float auxpreco = 0;
            DialogResult result = new DialogResult();

            this.Cursor = Cursors.WaitCursor;
            if (forneceOK())
            {
                sqlCommandText = "SELECT * FROM produto LEFT OUTER JOIN fornece ON (cod_prod = cod_produto) " +
                        "WHERE nome_prod ILIKE '%" + txtProduct.Text + "%' " +
                        "ORDER BY nome_prod ";

                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.Connection = standardDBConnection.cnn;
                sqlCommand.CommandText = sqlCommandText;
                databaseDataReader = sqlCommand.ExecuteReader();

                lstFoundProducts.Items.Clear();
                if (!databaseDataReader.HasRows)
                {
                    result = MessageBox.Show("Product não cadastrado! Deseja cadastrá-lo?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (result == DialogResult.Yes)
                    {
                        frmCadProdutos f = new frmCadProdutos();
                        f.ShowDialog();
                    }
                }
                else
                {
                    while (databaseDataReader.HasRows)
                    {
                        databaseDataReader.Read();
                        aux = databaseDataReader["nome_prod"].ToString();
                        auxcodigo = Convert.ToInt32(databaseDataReader["cod_prod"].ToString());
                        try
                        {
                            auxpreco = Convert.ToSingle(databaseDataReader["preco_custo"].ToString());
                            lstFoundProducts.Items.Add(string.Format("{0:00000} - {1}  R$ {2:###.00}", auxcodigo, aux, auxpreco));
                        }
                        catch
                        {
                            frmQtdSale f = new frmQtdSale(aux, true);
                            f.FormClosing += (ClosingSender, ClosingE) => auxpreco = f.GetPreco_custo();
                            f.ShowDialog();
                            lstFoundProducts.Items.Add(string.Format("{0:00000} - {1}  R$ {2:###.00}", auxcodigo, aux, auxpreco));
                        }
                    }
                }

                databaseDataReader.Close();
                databaseDataReader.Dispose();
                sqlCommand.Dispose();

                this.Cursor = Cursors.Default;
            }
            else
            {
                MessageBox.Show("Erro de Banco de Dados!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Cursor = Cursors.Default;
            }
        }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            PesquisarProducts();
        }
        private void txtProduct_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                PesquisarProducts();
        }

        //Coloca o Product na lista de compra
        private void SelectProduct()
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            NpgsqlDataReader databaseDataReader;
            string aux, sqlCommandText;
            int auxcodigo, qtd = 0;
            float auxpreco, precoTotal;
            frmQtdSale qtdForm;

            if (lstFoundProducts.SelectedIndex == -1)
            {
                MessageBox.Show("Por favor, selecione um produto.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            else
            {
                try
                {
                    try
                    {
                        auxcodigo = Convert.ToInt32(lstFoundProducts.Items[lstFoundProducts.SelectedIndex].ToString().Substring(0, 5));
                    }
                    catch
                    {
                        auxcodigo = 0;
                        MessageBox.Show("Erro!Selecione um Product!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                    if (auxcodigo != 0)
                    {
                        //SELECT * FROM produto LEFT OUTER JOIN fornece ON (cod_prod = cod_produto)
                        sqlCommandText = "SELECT * FROM produto FULL OUTER JOIN fornece ON (cod_prod = cod_produto) " +
                                "FULL OUTER JOIN fornecedor ON(cnpj_forn = cnpj) " +
                                "GROUP BY cod_prod, fornece.data, fornecedor.cnpj " +
                                "HAVING produto.cod_prod = " + auxcodigo.ToString();// " AND preco_custo = MAX(preco_custo)";

                        sqlCommand.CommandType = CommandType.Text;
                        sqlCommand.CommandText = sqlCommandText;
                        sqlCommand.Connection = standardDBConnection.cnn;
                        databaseDataReader = sqlCommand.ExecuteReader();

                        if (databaseDataReader.HasRows)
                        {
                            databaseDataReader.Read();
                            aux = databaseDataReader["nome_prod"].ToString();

                            qtdForm = new frmQtdSale(aux);
                            qtdForm.FormClosing += (closingSender, closingE) => { qtd = qtdForm.GetQtd(); };
                            qtdForm.ShowDialog();

                            auxcodigo = Convert.ToInt32(databaseDataReader["cod_prod"].ToString());
                            try
                            {
                                auxpreco = GetPrice(lstFoundProducts, lstFoundProducts.SelectedIndex);
                                this.selectedProductsPrices.Add(auxpreco);
                                precoTotal = auxpreco * qtd;

                                this.selectedProductsAmounts.Add(qtd);
                                this.selectedProductsCodes.Add(auxcodigo);
                                lstSelected.Items.Add(string.Format("{0:00000} - {1}  R$ {2:###.00} x {3:000} = R$ {4:####.00}", auxcodigo, aux, auxpreco, qtd, precoTotal));
                            }
                            catch
                            {
                                MessageBox.Show("Como o produto nunca foi fornecido, não será calculado corretamente", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                                precoTotal = 0;
                                auxpreco = 0;
                                this.selectedProductsAmounts.Add(qtd);
                                this.selectedProductsCodes.Add(auxcodigo);
                                this.selectedProductsPrices.Add(0);
                                lstSelected.Items.Add(string.Format("{0:00000} - {1}  R$ {2:###.00} x {3:000} = R$ {4:####.00}", auxcodigo, aux, auxpreco, qtd, precoTotal));
                            }


                            TotalRefresh();
                        }
                        databaseDataReader.Close();
                        databaseDataReader.Dispose();
                        sqlCommand.Dispose();
                    }

                }
                catch
                {
                    MessageBox.Show("Erro Desconhecido", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
        }
        private void btnSelect_Click(object sender, EventArgs e)
        {
            SelectProduct();
        }
        private void lstProduct_DoubleClick(object sender, EventArgs e)
        {
            SelectProduct();
        }
        private void lstProduct_KeyDown(object sender, KeyEventArgs e)
        {
            SelectProduct();
        }

        //Botões
        //Limpa a Pesquisa de Products
        private void btnClean_Click(object sender, EventArgs e)
        {
            lstFoundProducts.Items.Clear();
        }
        //Remove o Product Selecionado da compra
        private void btnRemove_Click(object sender, EventArgs e)
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            NpgsqlDataReader databaseDataReader;
            string sqlCommandText;
            int auxcodigo;

            if (lstFoundProducts.SelectedIndex == -1)
            {
                MessageBox.Show("Por favor, selecione um produto que deseja remover.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            else
            {
                auxcodigo = Convert.ToInt32(lstSelected.Items[lstSelected.SelectedIndex].ToString().Substring(0, 5));
                sqlCommandText = "SELECT * FROM produto WHERE cod_prod = " + auxcodigo.ToString();

                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlCommandText;
                sqlCommand.Connection = standardDBConnection.cnn;
                databaseDataReader = sqlCommand.ExecuteReader();

                selectedProductsAmounts.RemoveAt(lstSelected.SelectedIndex);
                selectedProductsPrices.RemoveAt(lstSelected.SelectedIndex);
                selectedProductsCodes.RemoveAt(lstSelected.SelectedIndex);
                lstSelected.Items.RemoveAt(lstSelected.SelectedIndex);
                TotalRefresh();

                databaseDataReader.Close();
                databaseDataReader.Dispose();
                sqlCommand.Dispose();
            }
        }
        //Limpa o formulário
        private void btnNew_Click(object sender, EventArgs e)
        {
            CleanFields();
        }
        //Salva a compra
        private void btnSavePurchase_Click(object sender, EventArgs e)
        {
            NpgsqlCommand sqlCommand = new NpgsqlCommand();
            string sqlFornece;
            DialogResult resp = new DialogResult();
            DateTime agora = DateTime.Now;

            if (lblForn.Text != "")
            {
                resp = MessageBox.Show("Confirmar Compra de " + lblTotal.Text + " ?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (resp == DialogResult.Yes)
                {

                    this.Cursor = Cursors.WaitCursor;


                    for (int count = 0; count < lstSelected.Items.Count; count++)
                    {
                        sqlFornece = "INSERT INTO fornece (data, quantidade, cnpj_forn, cod_produto, preco_custo) VALUES('" +
                                agora.ToString("dd/MM/yyyy") + "', " + selectedProductsAmounts[count] + ", '" + txtCNPJ.Text + "', " + selectedProductsCodes[count] + ", " + selectedProductsPrices[count] + ");";

                        sqlCommand.CommandType = CommandType.Text;
                        sqlCommand.CommandText = sqlFornece;
                        sqlCommand.Connection = standardDBConnection.cnn;
                        sqlCommand.ExecuteNonQuery();
                        sqlCommand.Dispose();
                    }
                    this.Cursor = Cursors.Default;
                    //MessageBox com valor total
                    MessageBox.Show("Sale de " + lblTotal.Text + ": Confirmada!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //CleanForm
                    CleanFields();

                }
            }
        }
        //Cancela a compra e fecha o formulário
        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

    }
}
